from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建数据库引擎 - 修改为MySQL连接
# 格式：mysql+pymysql://用户名:密码@主机地址:端口/数据库名
DATABASE_URI = 'mysql+pymysql://root:root@192.168.100.3:3306/test'
engine = create_engine(DATABASE_URI)

# 创建会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 声明基类
Base = declarative_base()


# 定义用户模型
class User(Base):
    __tablename__ = 'students'
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, index=True)  # MySQL需要指定String长度
    email = Column(String(100), unique=True, index=True)
    hashed_password = Column(String(100))

    def to_dict(self):
        return {
            'id': self.id,
            'username': self.username,
            'email': self.email,
            'hashed_password': self.hashed_password
        }


# 创建数据库表
Base.metadata.create_all(bind=engine)


# 用户操作类
class UserMapper:
    def __init__(self):
        self.db = SessionLocal()

    def __del__(self):
        self.db.close()  # 确保关闭数据库连接

    def get_user_by_id(self, user_id: int):
        return self.db.query(User).filter(User.id == user_id).first()

    def get_user_by_username(self, username: str):
        return self.db.query(User).filter(User.username == username).first()

    def create_user(self, username: str, email: str, hashed_password: str):
        db_user = User(username=username, email=email, hashed_password=hashed_password)
        self.db.add(db_user)
        self.db.commit()
        self.db.refresh(db_user)
        return db_user

    def update_user(self,  username: str, email: str, hashed_password: str):
        user = self.get_user_by_username(username)
        if user:
            user.email = email
            user.hashed_password = hashed_password
            self.db.commit()
            self.db.refresh(user)
        return user

    def delete_user_byid(self, user_id: int):
        user = self.get_user_by_id(user_id)
        if user:
            self.db.delete(user)
            self.db.commit()
        return user
    def delete_user_byusername(self, username: str):
        user = self.get_user_by_username(username)
        if user:
            self.db.delete(user)
            self.db.commit()
        return user


def get_user_mapper():
    return UserMapper()
